<?php
class Trial extends Admin_Controller
{
	
	function __construct()
	{
		parent::__construct();
		$this->load->model('fund_model','fund');
		$this->load->model('voucher_model','voucher');
		$this->load->model('voucher_balance_model','balance');
		$this->load->model('voucher_check_model','check');
		$this->load->model('voucher_receive_paid_model','receive_paid');
		$this->load->model('voucher_detail_model','voucher_detail');
	}
	
	//**** งบทดลอง ******
	function index($mode,$id)
	{
		$data['fund'] = $this->fund->get_row($id);
		$data['mode'] = $mode;
		$user = login_data();
		$agency_id = (level_data('view'))?(isset($_GET['agency_id']))?$_GET['agency_id']:$user['agency_id']:$user['agency_id'];
		//**************Start where section ******************
		$where = '';
		list($year,$month,$data['year'],$data['month']) = last_voucher_date($id);
		$data['agency_id'] = $agency_id;
		list($start_date,$end_date) = year_budget($data['year']);
		$data['start_date'] = $start_date;
		$data['end_date'] = $end_date;
		$where = ' and vouchers.voucher_date between \''.$start_date.'\' and \''.$end_date.'\'';
		$balance_where = ' and YEAR(vouchers.voucher_date) = '.($data['year']-1).' and MONTH(vouchers.voucher_date) = 10';
		
		if($mode=='month')
		{
			//$month = (!empty($_GET['month']))?$_GET['month']:date('m');
			$where = ' and YEAR(vouchers.voucher_date) = '.$year.' and MONTH(vouchers.voucher_date) = '.$month;		
			$balance_where = ' and YEAR(vouchers.voucher_date) = '.$year.' and MONTH(vouchers.voucher_date) = '.$month;		
		}
		
		if(!empty($agency_id))
		{
			$where .= ' and vouchers.agency_id = '.$agency_id;
			$balance_where .= ' and vouchers.agency_id = '.$agency_id;
		}
		
		//**************End where section ******************
		//$this->db->debug = TRUE;
		$data['result'] = $this->voucher_detail->get('
		select accounts.name,accounts.code,COALESCE(c.dr_balance,0) dr_balance,COALESCE(d.cr_balance,0) cr_balance,COALESCE(a.dr_month,0) dr_month,COALESCE(b.cr_month,0) cr_month,
		(CASE when (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(dr_month,0) - COALESCE(cr_month,0)) < 0 then 0 else (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(dr_month,0) - COALESCE(cr_month,0)) end) dr_sum,
		(CASE when (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(dr_month,0) - COALESCE(cr_month,0)) < 0 then (COALESCE(c.dr_balance,0) - COALESCE(d.cr_balance,0) + COALESCE(dr_month,0) - COALESCE(cr_month,0)) * -1 else 0 end) cr_sum 
		from accounts
		left join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null ) '.$where.' group by account_id) a
		on a.account_id = accounts.id
		left join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and mode is null ) '.$where.' group by account_id) b
		on b.account_id = accounts.id
		left join (select account_id,sum(price) dr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null ) '.$balance_where.' group by account_id) c
		on c.account_id = accounts.id
		left join (select account_id,sum(price) cr_balance from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance = 1 and mode is null ) '.$balance_where.' group by account_id) d
		on d.account_id = accounts.id
		where fund_id = '.$id.'
		order by accounts.code asc
		',FALSE);
		//$this->db->debug = FALSE;
		$this->template->append_metadata(js_print());
		$this->template->build('voucher_trial',$data);
	}
	
}
?>